package net.suncaper.psychological.mapper.impl;

import net.suncaper.psychological.mapper.AppointmentMapper;
import net.suncaper.psychological.model.domain.Appointment;
import net.suncaper.psychological.common.utils.MyDbUtil;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AppointmentMapperImpl implements AppointmentMapper {
    @Override
    public int insertAppointment(Appointment appointment) {
        String sql = "INSERT INTO appointment(student_id, type, duty_id, status, apply_time, scheduled_time, scheduled_place) VALUES (?, ?, ?, ?, ?, ?, ?)";
        return MyDbUtil.executeUpdate(sql,
            appointment.getStudent_id(),
            appointment.getType(),
            appointment.getDuty_id(),
            appointment.getStatus(),
            appointment.getApply_time(),
            appointment.getScheduled_time(),
            appointment.getScheduled_place()
        );
    }

    @Override
    public Appointment selectAppointmentByStudentId(String studentId) {
        try {
            System.out.println("执行SQL查询，学号: " + studentId);
            
            // 先通过学号查找用户ID
            String userSql = "SELECT id FROM user WHERE username = ?";
            Connection conn = MyDbUtil.getConnection();
            PreparedStatement userStmt = null;
            ResultSet userRs = null;
            Long userId = null;
            
            try {
                userStmt = conn.prepareStatement(userSql);
                userStmt.setString(1, studentId);
                userRs = userStmt.executeQuery();
                
                if (userRs.next()) {
                    userId = userRs.getLong("id");
                    System.out.println("找到用户ID: " + userId + " (学号: " + studentId + ")");
                } else {
                    System.out.println("未找到学号对应的用户: " + studentId);
                    return null;
                }
            } catch (SQLException e) {
                System.out.println("查询用户信息异常: " + e.getMessage());
                e.printStackTrace();
                throw new RuntimeException("数据库查询异常", e);
            } finally {
                if (userRs != null) {
                    try {
                        userRs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (userStmt != null) {
                    try {
                        userStmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
            
            // 使用用户ID查询预约信息
            String sql = "SELECT id, student_id, type, duty_id, status, apply_time, approve_time, reviewer_id, scheduled_time, scheduled_place, cancel_reason, is_urgent FROM appointment WHERE student_id = ? ORDER BY apply_time DESC LIMIT 1";
            System.out.println("SQL语句: " + sql);
            
            PreparedStatement stmt = null;
            ResultSet rs = null;
            try {
                stmt = conn.prepareStatement(sql);
                stmt.setLong(1, userId);
                rs = stmt.executeQuery();
                
                if (rs.next()) {
                    Appointment appointment = new Appointment();
                    appointment.setId(rs.getLong("id"));
                    appointment.setStudent_id(rs.getLong("student_id"));
                    appointment.setType(rs.getString("type"));
                    appointment.setDuty_id(rs.getLong("duty_id"));
                    appointment.setStatus(rs.getString("status"));
                    appointment.setApply_time(rs.getString("apply_time"));
                    appointment.setApprove_time(rs.getString("approve_time"));
                    
                    Long reviewerId = rs.getLong("reviewer_id");
                    if (!rs.wasNull()) {
                        appointment.setReviewer_id(reviewerId);
                    }
                    
                    appointment.setScheduled_time(rs.getString("scheduled_time"));
                    appointment.setScheduled_place(rs.getString("scheduled_place"));
                    appointment.setCancel_reason(rs.getString("cancel_reason"));
                    appointment.setIs_urgent(rs.getBoolean("is_urgent"));
                    
                    System.out.println("找到预约: ID=" + appointment.getId() + ", 用户ID=" + appointment.getStudent_id());
                    return appointment;
                } else {
                    System.out.println("未找到预约信息");
                    return null;
                }
            } catch (SQLException e) {
                System.out.println("SQL执行异常: " + e.getMessage());
                e.printStackTrace();
                throw new RuntimeException("数据库查询异常", e);
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                MyDbUtil.closeConnection();
            }
        } catch (Exception e) {
            System.out.println("查询预约异常: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }

    @Override
    public boolean cancelAppointment(Long appointmentId, String cancelReason) {
        try {
            System.out.println("执行取消预约，ID: " + appointmentId + ", 原因: " + cancelReason);
            
            // 首先查询预约信息，检查时间限制
            String selectSql = "SELECT scheduled_time FROM appointment WHERE id = ?";
            Connection conn = MyDbUtil.getConnection();
            PreparedStatement selectStmt = null;
            ResultSet rs = null;
            
            try {
                selectStmt = conn.prepareStatement(selectSql);
                selectStmt.setLong(1, appointmentId);
                rs = selectStmt.executeQuery();
                
                if (rs.next()) {
                    java.sql.Timestamp scheduledTime = rs.getTimestamp("scheduled_time");
                    if (scheduledTime == null) {
                        System.out.println("预约时间为空，无法取消");
                        return false;
                    }
                    
                    // 计算时间差
                    java.sql.Timestamp currentTime = new java.sql.Timestamp(System.currentTimeMillis());
                    
                    long timeDiff = scheduledTime.getTime() - currentTime.getTime();
                    long hoursDiff = timeDiff / (1000 * 60 * 60);
                    
                    System.out.println("距离预约时间还有: " + hoursDiff + " 小时");
                    
                    if (hoursDiff < 24) {
                        System.out.println("时间不足24小时，无法取消");
                        throw new RuntimeException("距离预约时间小于24小时，无法取消预约");
                    }
                    
                    // 执行取消操作
                    String updateSql = "UPDATE appointment SET status = 'CANCELED', cancel_reason = ? WHERE id = ?";
                    PreparedStatement updateStmt = conn.prepareStatement(updateSql);
                    updateStmt.setString(1, cancelReason);
                    updateStmt.setLong(2, appointmentId);
                    
                    int result = updateStmt.executeUpdate();
                    updateStmt.close();
                    
                    System.out.println("取消预约结果: " + result);
                    return result > 0;
                } else {
                    System.out.println("未找到预约记录");
                    return false;
                }
            } catch (SQLException e) {
                System.out.println("SQL执行异常: " + e.getMessage());
                e.printStackTrace();
                throw new RuntimeException("数据库操作异常", e);
            } finally {
                if (rs != null) {
                    try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
                }
                if (selectStmt != null) {
                    try { selectStmt.close(); } catch (SQLException e) { e.printStackTrace(); }
                }
                MyDbUtil.closeConnection();
            }
        } catch (Exception e) {
            System.out.println("取消预约异常: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }

    @Override
    public List<Appointment> selectHistoryAppointmentsByStudentId(String studentId) {
        try {
            System.out.println("查询历史预约记录，学号: " + studentId);
            
            // 先通过学号查找用户ID
            String userSql = "SELECT id FROM user WHERE username = ?";
            Connection conn = MyDbUtil.getConnection();
            PreparedStatement userStmt = null;
            ResultSet userRs = null;
            Long userId = null;
            
            try {
                userStmt = conn.prepareStatement(userSql);
                userStmt.setString(1, studentId);
                userRs = userStmt.executeQuery();
                
                if (userRs.next()) {
                    userId = userRs.getLong("id");
                    System.out.println("找到用户ID: " + userId + " (学号: " + studentId + ")");
                } else {
                    System.out.println("未找到学号对应的用户: " + studentId);
                    return new ArrayList<>();
                }
            } catch (SQLException e) {
                System.out.println("查询用户信息异常: " + e.getMessage());
                e.printStackTrace();
                throw new RuntimeException("数据库查询异常", e);
            } finally {
                if (userRs != null) {
                    try {
                        userRs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (userStmt != null) {
                    try {
                        userStmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
            
            // 查询除了最新记录外的所有历史记录
            String sql = "SELECT id, student_id, type, duty_id, status, apply_time, approve_time, reviewer_id, scheduled_time, scheduled_place, cancel_reason, is_urgent FROM appointment WHERE student_id = ? AND id < (SELECT MAX(id) FROM appointment WHERE student_id = ?) ORDER BY apply_time DESC";
            System.out.println("SQL语句: " + sql);
            
            PreparedStatement stmt = null;
            ResultSet rs = null;
            List<Appointment> appointments = new ArrayList<>();
            
            try {
                stmt = conn.prepareStatement(sql);
                stmt.setLong(1, userId);
                stmt.setLong(2, userId);
                rs = stmt.executeQuery();
                
                while (rs.next()) {
                    Appointment appointment = new Appointment();
                    appointment.setId(rs.getLong("id"));
                    appointment.setStudent_id(rs.getLong("student_id"));
                    appointment.setType(rs.getString("type"));
                    appointment.setDuty_id(rs.getLong("duty_id"));
                    appointment.setStatus(rs.getString("status"));
                    appointment.setApply_time(rs.getString("apply_time"));
                    appointment.setApprove_time(rs.getString("approve_time"));
                    
                    Long reviewerId = rs.getLong("reviewer_id");
                    if (!rs.wasNull()) {
                        appointment.setReviewer_id(reviewerId);
                    }
                    
                    appointment.setScheduled_time(rs.getString("scheduled_time"));
                    appointment.setScheduled_place(rs.getString("scheduled_place"));
                    appointment.setCancel_reason(rs.getString("cancel_reason"));
                    appointment.setIs_urgent(rs.getBoolean("is_urgent"));
                    
                    appointments.add(appointment);
                    System.out.println("找到历史预约: ID=" + appointment.getId() + ", 学号=" + appointment.getStudent_id());
                }
                
                System.out.println("历史预约记录数量: " + appointments.size());
                return appointments;
            } catch (SQLException e) {
                System.out.println("SQL执行异常: " + e.getMessage());
                e.printStackTrace();
                throw new RuntimeException("数据库查询异常", e);
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                MyDbUtil.closeConnection();
            }
        } catch (Exception e) {
            System.out.println("查询历史预约异常: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }

    @Override
    public List<Appointment> selectAllAppointmentsByStudentId(String studentId) {
        try {
            System.out.println("查询全部预约记录，学号: " + studentId);
            
            // 先通过学号查找用户ID
            String userSql = "SELECT id FROM user WHERE username = ?";
            Connection conn = MyDbUtil.getConnection();
            PreparedStatement userStmt = null;
            ResultSet userRs = null;
            Long userId = null;
            
            try {
                userStmt = conn.prepareStatement(userSql);
                userStmt.setString(1, studentId);
                userRs = userStmt.executeQuery();
                
                if (userRs.next()) {
                    userId = userRs.getLong("id");
                    System.out.println("找到用户ID: " + userId + " (学号: " + studentId + ")");
                } else {
                    System.out.println("未找到学号对应的用户: " + studentId);
                    return new ArrayList<>();
                }
            } catch (SQLException e) {
                System.out.println("查询用户信息异常: " + e.getMessage());
                e.printStackTrace();
                throw new RuntimeException("数据库查询异常", e);
            } finally {
                if (userRs != null) {
                    try {
                        userRs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (userStmt != null) {
                    try {
                        userStmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
            
            // 查询该学号的所有记录，按id从大到小排序
            String sql = "SELECT id, student_id, type, duty_id, status, apply_time, approve_time, reviewer_id, scheduled_time, scheduled_place, cancel_reason, is_urgent FROM appointment WHERE student_id = ? ORDER BY id DESC";
            System.out.println("SQL语句: " + sql);
            
            PreparedStatement stmt = null;
            ResultSet rs = null;
            List<Appointment> appointments = new ArrayList<>();
            
            try {
                stmt = conn.prepareStatement(sql);
                stmt.setLong(1, userId);
                rs = stmt.executeQuery();
                
                while (rs.next()) {
                    Appointment appointment = new Appointment();
                    appointment.setId(rs.getLong("id"));
                    appointment.setStudent_id(rs.getLong("student_id"));
                    appointment.setType(rs.getString("type"));
                    appointment.setDuty_id(rs.getLong("duty_id"));
                    appointment.setStatus(rs.getString("status"));
                    appointment.setApply_time(rs.getString("apply_time"));
                    appointment.setApprove_time(rs.getString("approve_time"));
                    
                    Long reviewerId = rs.getLong("reviewer_id");
                    if (!rs.wasNull()) {
                        appointment.setReviewer_id(reviewerId);
                    }
                    
                    appointment.setScheduled_time(rs.getString("scheduled_time"));
                    appointment.setScheduled_place(rs.getString("scheduled_place"));
                    appointment.setCancel_reason(rs.getString("cancel_reason"));
                    appointment.setIs_urgent(rs.getBoolean("is_urgent"));
                    
                    appointments.add(appointment);
                    System.out.println("找到预约: ID=" + appointment.getId() + ", 学号=" + appointment.getStudent_id());
                }
                
                System.out.println("全部预约记录数量: " + appointments.size());
                return appointments;
            } catch (SQLException e) {
                System.out.println("SQL执行异常: " + e.getMessage());
                e.printStackTrace();
                throw new RuntimeException("数据库查询异常", e);
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                MyDbUtil.closeConnection();
            }
        } catch (Exception e) {
            System.out.println("查询全部预约异常: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }

    @Override
    public boolean updateAppointmentStatus(Long appointmentId, String status) {
        try {
            String sql = "UPDATE appointment SET status = ? WHERE id = ?";
            int result = MyDbUtil.executeUpdate(sql, status, appointmentId);
            System.out.println("更新预约状态: appointmentId=" + appointmentId + ", status=" + status + ", result=" + result);
            return result > 0;
        } catch (Exception e) {
            System.out.println("更新预约状态异常: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public int updateAppointmentArrangement(Long appointmentId, String scheduledTime, String scheduledPlace) {
        try {
            String sql = "UPDATE appointment SET scheduled_time = ?, scheduled_place = ? WHERE id = ?";
            int result = MyDbUtil.executeUpdate(sql, scheduledTime, scheduledPlace, appointmentId);
            System.out.println("更新预约安排: appointmentId=" + appointmentId + ", scheduledTime=" + scheduledTime + ", scheduledPlace=" + scheduledPlace + ", result=" + result);
            return result;
        } catch (Exception e) {
            System.out.println("更新预约安排异常: " + e.getMessage());
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public List<Appointment> selectApprovedInitialAppointments() {
        try {
            System.out.println("查询已批准的初访预约");
            
            String sql = "SELECT id, student_id, type, duty_id, status, apply_time, approve_time, reviewer_id, scheduled_time, scheduled_place, cancel_reason, is_urgent FROM appointment WHERE type = 'INITIAL' AND status = 'APPROVED' ORDER BY scheduled_time ASC";
            System.out.println("SQL语句: " + sql);
            
            Connection conn = MyDbUtil.getConnection();
            PreparedStatement stmt = null;
            ResultSet rs = null;
            List<Appointment> appointments = new ArrayList<>();
            
            try {
                stmt = conn.prepareStatement(sql);
                rs = stmt.executeQuery();
                
                while (rs.next()) {
                    Appointment appointment = new Appointment();
                    appointment.setId(rs.getLong("id"));
                    appointment.setStudent_id(rs.getLong("student_id"));
                    appointment.setType(rs.getString("type"));
                    appointment.setDuty_id(rs.getLong("duty_id"));
                    appointment.setStatus(rs.getString("status"));
                    appointment.setApply_time(rs.getString("apply_time"));
                    appointment.setApprove_time(rs.getString("approve_time"));
                    
                    Long reviewerId = rs.getLong("reviewer_id");
                    if (!rs.wasNull()) {
                        appointment.setReviewer_id(reviewerId);
                    }
                    
                    appointment.setScheduled_time(rs.getString("scheduled_time"));
                    appointment.setScheduled_place(rs.getString("scheduled_place"));
                    appointment.setCancel_reason(rs.getString("cancel_reason"));
                    appointment.setIs_urgent(rs.getBoolean("is_urgent"));
                    
                    appointments.add(appointment);
                    System.out.println("找到已批准的初访预约: ID=" + appointment.getId() + ", 学号=" + appointment.getStudent_id());
                }
                
                System.out.println("已批准的初访预约数量: " + appointments.size());
                return appointments;
            } catch (SQLException e) {
                System.out.println("SQL执行异常: " + e.getMessage());
                e.printStackTrace();
                throw new RuntimeException("数据库查询异常", e);
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                MyDbUtil.closeConnection();
            }
        } catch (Exception e) {
            System.out.println("查询已批准的初访预约异常: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }
} 